Create Measures



Note: Please follow the following gif  to create the Calculated Measures table and then create the measures using the formulas down below




How to Calculate Measures

=> Total Deceased = DISTINCTCOUNT(PoliceInvolvedFatalities[UID])

 

=> Cnt of Months = DISTINCTCOUNT('Date'[YearMonthShort])

 

 

=> Fatalities Per Month = DIVIDE([Total Deceased],[Cnt of months],0)+0

 

 

=> Fatalities Per Month YoY =

VAR __PREV_YEAR = CALCULATE([Fatalities Per Month], DATEADD('Date'[Date], -1, YEAR))

RETURN

    DIVIDE([Fatalities Per Month] - __PREV_YEAR, __PREV_YEAR,0)

 

 

=> Indicator = If([Fatalities Per Month YoY%]>0,UNICHAR(11165),UNICHAR(11167))

 

=> KPI Data Color = If([Fatalities Per Month YoY%]>0,1,0)



=> MapColors = SWITCH(

    TRUE(),

     [Fatalities Per Month YoY%] >0

        &&   [Fatalities Per Month YoY%] <= 0.5, "#FFF176",

          [Fatalities Per Month YoY%] >0.5

        &&   [Fatalities Per Month YoY%] <= 1.0, "#FFA726",

 

   

     [Fatalities Per Month YoY%]> 1.0,"#BF360C",

   

    [Fatalities Per Month YoY%] <=0 &&  [Fatalities Per Month YoY%]<=-0.5,"#1B5E20",

   "#8BC34A"

     

   )


Calculated columns


Age Range =

SWITCH (

    TRUE (),

    

    'PoliceInvolvedFatalities'[Age] >= 0

        && 'PoliceInvolvedFatalities'[Age] <= 18, "0-18",

   

    'PoliceInvolvedFatalities'[Age] > 18

        && 'PoliceInvolvedFatalities'[Age] <= 40, "19-40",

    'PoliceInvolvedFatalities'[Age] > 40

        && 'PoliceInvolvedFatalities'[Age] <= 50, "41-50",

    "51 & over"

 

   

)


Note: Copy and  paste the above DAX to add  "Age Range" column



 


 

 

M vs Dax vs Measures vs Calculated columns